using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
using System;

namespace Customs.Components.GC
{
	public class HopDong
	{
		public static DataSet GetDanhSach(string maHaiQuan, string maDoanhNghiep)
		{
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_SLXNK"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT NULL		AS SoTiepNhan, ");
            query.Append("HD.So_HD			AS SoHopDong, ");
            query.Append("HD.Ma_HQHD		AS MaHaiQuan, ");
            query.Append("HD.DVGC			AS MaDoanhNghiep, ");
            query.Append("HD.Ngay_Ky		AS NgayKy, ");
            query.Append("HD.Ngay_HH		AS NgayHetHan, ");
            query.Append("HD.Ngay_GH		AS NgayGiaHan, ");
            query.Append("HD.NuocThueGC		AS NuocThue_ID, ");
            query.Append("HD.NgTe			AS NguyenTe_ID ");
            query.Append("FROM DHDGC HD ");
            query.Append("WHERE HD.Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep");

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char);
            parameters[0].Size = 6;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[1].Size = 14;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maDoanhNghiep;

            return SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
	
		}

	
        public static DataSet GetHopDong(string maHaiQuan, string maDoanhNghiep,string sohopdong,DateTime ngayky)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_SLXNK"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT NULL		AS SoTiepNhan, ");
            query.Append("HD.So_HD			AS SoHopDong, ");
            query.Append("HD.Ma_HQHD		AS MaHaiQuan, ");
            query.Append("HD.DVGC			AS MaDoanhNghiep, ");
            query.Append("HD.Ngay_Ky		AS NgayKy, ");
            query.Append("HD.Ngay_HH		AS NgayHetHan, ");
            query.Append("HD.Ngay_GH		AS NgayGiaHan, ");
            query.Append("HD.NuocThueGC		AS NuocThue_ID, ");
            query.Append("HD.NgTe			AS NguyenTe_ID ");
            query.Append("FROM DHDGC HD ");
            query.Append("WHERE HD.Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep");
            query.Append(" AND HD.Ngay_Ky = @Ngay_Ky AND So_HD = @So_HD");

            SqlParameter[] parameters = new SqlParameter[4];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char);
            parameters[0].Size = 6;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[1].Size = 14;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maDoanhNghiep;

            parameters[2] = new SqlParameter("@Ngay_Ky", SqlDbType.DateTime);            
            parameters[2].Direction = ParameterDirection.Input;
            parameters[2].Value = ngayky;

            parameters[3] = new SqlParameter("@So_HD", SqlDbType.VarChar);            
            parameters[3].Direction = ParameterDirection.Input;
            parameters[3].Value = sohopdong;

            return SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
        }

        //lay danh sach thong tin cua mot hop dong tu hai quan ve
        public static DataSet GetHopDong(long index, string maHaiQuan, string maDoanhNghiep)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_SLXNK"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT * from ( ");
            query.Append("SELECT NULL		AS SoTiepNhan, ");
            query.Append("HD.So_HD			AS SoHopDong, ");
            query.Append("HD.Ma_HQHD		AS MaHaiQuan, ");
            query.Append("HD.DVGC			AS MaDoanhNghiep, ");
            query.Append("HD.Ngay_Ky		AS NgayKy, ");
            query.Append("HD.Ngay_HH		AS NgayHetHan, ");
            query.Append("HD.Ngay_GH		AS NgayGiaHan, ");
            query.Append("HD.NuocThueGC		AS NuocThue_ID, ");
            query.Append("HD.NgTe			AS NguyenTe_ID, ");
            query.Append("row_number() OVER (ORDER BY So_HD) AS rowid ");            
            query.Append("FROM DHDGC HD ");
            query.Append("WHERE HD.Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep ");
            query.Append(" )a where rowid=@rowid ORDER BY SoHopDong");            

            SqlParameter[] parameters = new SqlParameter[3];

            parameters[0] = new SqlParameter("@rowid", SqlDbType.BigInt);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = index;
            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char);
            parameters[1].Size = 6;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maHaiQuan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Size = 14;
            parameters[2].Direction = ParameterDirection.Input;
            parameters[2].Value = maDoanhNghiep;

            DataSet ds= SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
            if(ds.Tables[0].Rows.Count==0)
                return null;
            return ds;
  
        }
//		public static DataSet GetDanhSachFull(string maHaiQuan, string maDoanhNghiep)
//		{
//			DataSet ds = new DataSet("HopDong");
//			using (SqlConnection conn = new SqlConnection(ConnectionManager.GetConnectionString("SLXNK", maHaiQuan)))
//			{
//				conn.Open();
//				using (SqlTransaction transaction = conn.BeginTransaction())
//				{
//					DataTable dtHopDong = GetDanhSach(transaction, maHaiQuan, maDoanhNghiep).Tables[0];
//					dtHopDong.TableName = "HopDong";
//					ds.Tables.Add(dtHopDong);
//				}
//			}
//			return ds;
//		}
	}
}